import json
from datetime import datetime
import requests
from urllib.parse import urljoin
import pymysql
#海事一网通管-行政处罚监管业务-处罚查询统计-案件查询
BASE_URL = 'https://xzcf.msa.gov.cn/cttic-egov-aps/apsStatistical/getCaseStatisticalList'
GET_INFO_URL = 'https://xzcf.msa.gov.cn/cttic-egov-aps/components/caseDetail/getData?businessType=3&businessId='

headers = {
    "Content-Type": "application/json",
    "Authorization": "Bearer 88a8e5a7-7eea-4a70-b1ea-4e7e18f6f682"
}

cookies = {
    'yfx_c_g_u_id_10000002': '_ck25022511240416235651637801741',
    'yfx_c_g_u_id_10000003': '_ck25022511240917122176567334057',
    'yfx_f_l_v_t_10000002': 'f_t_1740453844608__r_t_1740453844608__v_t_1740469039717__r_c_0',
    'FSSO': 'e80a0f2736819063f6efc956c84fa14e1b2a0bd02e0a5e8206cb0d125d53dd01c333fe546adc763d57a4ceab4b5a0b3bbe653f25c2f7bca86e47753ec416f4225d1952c6946f34e152aee5121d1ff41b9893de963be51c56f2c8b7fea7ccef9adb1fb2b1fcdcc465',
    'yfx_f_l_v_t_10000003': 'f_t_1740453849712__r_t_1740882162892__v_t_1740882162892__r_c_5',
    'msa_token': '88a8e5a7-7eea-4a70-b1ea-4e7e18f6f682',
    'access_token': 'eyJhbGciOiJIUzI1NiJ9.eyJpYXQiOjE3NDA4ODIyMTAsImV4cCI6MTc0MDkxMTAxMCwic3ViIjoie1xuICBcInVzZXJJZFwiIDogXCI4NTU1NTcyNTAtMDEwMjAyMTQwMTAyMDUwNDA1MDFcIixcbiAgXCJwYXNzd29yZFwiIDogXCIkMmEkMTAkUnBGSmp4WWlYZEVzQUduV3AvOGZzT2V0TXVPT045Nk50ay9ZbTJNL1JLUnlVMEdac2VhRENcIixcbiAgXCJ1c2VyQ29kZVwiIDogXCIxNDIzMjIxOTg1MTIyNjcwMjktMDEwMjAyMTQwMTAyMDUwNDA1MDFcIixcbiAgXCJ1c2VyTmFtZUNuXCIgOiBcIuadjuWon1wiLFxuICBcImNsaWVudElwXCIgOiBcIlwiLFxuICBcImFwcElkXCIgOiBcIlwiLFxuICBcImFwcENvZGVcIiA6IFwiQ1RUSUNcIixcbiAgXCJvZmZpY2VJZFwiIDogXCI2MTJCQzBCODJDMzI0ODRGOUFEOTRDREQ1REE3QUM5RlwiLFxuICBcInN5c1JvbGVJZFwiIDogbnVsbCxcbiAgXCJvZmZpY2VOYW1lXCIgOiBcIua3seWcs-a1t-S6i-WxgFwiLFxuICBcInJvbGVJZHNcIiA6IFsgXCI4XCIgXSxcbiAgXCJwYXNzd29yZE1vZGlmeVRpbWVcIiA6IG51bGwsXG4gIFwiaXNQYXNzd29yZE1vZGlmeVwiIDogbnVsbCxcbiAgXCJ1c2VybmFtZVwiIDogXCIxNDIzMjIxOTg1MTIyNjcwMjktMDEwMjAyMTQwMTAyMDUwNDA1MDFcIixcbiAgXCJkZXB0T2ZmaWNlQ29kZVwiIDogbnVsbCxcbiAgXCJkZXB0T2ZmaWNlTmFtZVwiIDogbnVsbCxcbiAgXCJzZXR0bGVPZmZpY2VDb2RlXCIgOiBudWxsLFxuICBcInNldHRsZU9mZmljZU5hbWVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZU5hbWVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZUNvZGVcIiA6IG51bGwsXG4gIFwicm9sZU9mZmljZUlkXCIgOiBudWxsLFxuICBcIm9mZmljZUNvZGVcIiA6IFwiMDEwMjAyMTRcIixcbiAgXCJ1c2VyQ29va2llS2V5XCIgOiBcIjg1NTU1NzI1MC0wMTAyMDIxNDAxMDIwNTA0MDUwMV9DVFRJQ1wiLFxuICBcImRhdGFBdXRob3JpdHlJZFwiIDogbnVsbCxcbiAgXCJwcmluY2lwYWxPcmdDb2RlXCIgOiBcIua3seWcs-a1t-S6i-WxgFwiLFxuICBcIm9mZmljZVR5cGVcIiA6IG51bGwsXG4gIFwibWFuYWdlVHlwZVwiIDogbnVsbCxcbiAgXCJsb2dpblRpbWVcIiA6IG51bGwsXG4gIFwic3lzdGVtUmF0ZVwiIDogbnVsbCxcbiAgXCJob21lQ3VycmVuY3lcIiA6IG51bGwsXG4gIFwib2ZmaWNlTWRtSWRcIiA6IG51bGwsXG4gIFwiY29ycE1kbUlkXCIgOiBudWxsLFxuICBcImNvcnBTaG9ydENvZGVcIiA6IG51bGwsXG4gIFwic3lzUm9sZUxpc3RcIiA6IG51bGwsXG4gIFwiZW5hYmxlZFwiIDogZmFsc2UsXG4gIFwiYXV0aG9yaXRpZXNcIiA6IFsgXSxcbiAgXCJhY2NvdW50Tm9uRXhwaXJlZFwiIDogZmFsc2UsXG4gIFwiYWNjb3VudE5vbkxvY2tlZFwiIDogZmFsc2UsXG4gIFwiY3JlZGVudGlhbHNOb25FeHBpcmVkXCIgOiBmYWxzZVxufSJ9.uEf6vNHzZEVMBnt7K2-_8LDOQF6UtOOwms501TP82BQ'
}

data = {
    "generateBeginDate": "2024-10-01",
    "generateEndDate": "2024-12-31",
    'orgCode': "01020214",
    'orgCodeBool': 'true',
    'pageNo': 1,
    'pageSize':10000
}

#组装请求配置
session = requests.Session()

#请求指标11船舶污染物的接收和处理情况备案数量
response_lo = session.post(BASE_URL, headers=headers, json=data, cookies=cookies)

#响应回来的参数
#print("响应回来的参数:",response_lo.text)
#响应回来的参数文本转json
strData = json.loads(response_lo.text)
strRecordsData = strData['data']['records'] #获取到具体数据的位置

#获取数据情况records、total、size、current、pages
print("获取："+str(strData['data']['total'])+"条数据","获取："+str(strData['data']['pages'])+"页数据","获取每页设置"+str(strData['data']['size'])+"条数据")

#打印每一列数据的字段key
#print("获取建表字段：" , tuple(strRecordsData[0].keys())  )

if response_lo.status_code == 200:
    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'ywtg_ship_xingzhengchufa_23_43'
    table_parties = 'ywtg_ship_xingzhengchufa_parties_23_43'
    table_enforceRecord = 'ywtg_ship_xingzhengchufa_enforceRecord_23_43'
    # 连接数据库
    connection = pymysql.connect(**db_config)

try:
    # 开始事务
    with connection.cursor() as cursor:
        # 循环插入每条记录
        for record in strRecordsData:
            # 获取需要插入的字段名称
            fields = ', '.join(record.keys())
            # 判断是否包含 "index"，如果包含则替换为 "index1"
            if "index" in fields:
                fields = fields.replace("index", "index1")
            # 拼装需要插入字段对应的%号个数
            values = ", ".join(['%s'] * len(record))
            # 判断是否包含 "None"，如果包含则替换为 "null"
            if "None" in values:
                values = values.replace("None", "null")
            # print("!!!"+fields)
            # 查详情的partiesId
            partiesId = record['partiesId']
            # print("查详情的windowApplyId:",windowApply_id)

            # 构建插入语句，插入业务主表
            insert_stmt = (
                "INSERT INTO `{table}` ({fields}) "
                "VALUES ({values})"
            ).format(
                table=table_name,
                fields=fields,
                values=values
            )
            # 插入语句
            #print("插入SQl语句：", insert_stmt)
            #  print("插入值：", tuple(record.values()))
            print("插入行政处罚主表表:", tuple(parties.values()))
            # 执行sql语句插入数据，record.values()用于获取字段中的值
            cursor.execute(insert_stmt, tuple(record.values()))


            # 获取申请单审核员信息
            response_auditor = session.get(GET_INFO_URL + partiesId, cookies=cookies)
            partiesDataText = json.loads(response_auditor.text)
            # print("获取审查人详情:",auditorDataText['data']['acceptList'])

            # 获取审批的信息partiesList
            if partiesDataText['data']['caseReasonList'][0]['surveyReport']['partiesList'] is not None:
                partiesList = partiesDataText['data']['caseReasonList'][0]['surveyReport']['partiesList']
            else:
                partiesList = []  # 如果结构不符合预期，设置为空列表

            #获取调差人员的信息  enforceRecordList
            if partiesDataText['data']['caseReasonList'][0]['surveyReport']['enforceRecordList'] is not None:
                enforceRecordList = partiesDataText['data']['caseReasonList'][0]['surveyReport']['enforceRecordList']
            else:
                enforceRecordList = []  # 如果结构不符合预期，设置为空列表

            # 获取的成功状态
            if response_auditor.status_code == 200:
                #插入详情中间表

                for parties in partiesList:
                    # 获取需要插入的字段名称
                    parties_fields = ', '.join(parties.keys())
                    #print("获取审查人表格字段:", parties.keys())
                    print("插入立案中间表:", tuple(parties.values()))
                    # 拼装需要插入字段对应的%号个数
                    parties_values = ", ".join(['%s'] * len(parties))

                    insert_parties = (
                        "INSERT INTO `{table}` ({fields}) "
                        "VALUES ({values})"
                    ).format(
                        table=table_parties,
                        fields=parties_fields,
                        values=parties_values
                    )
                    #处理json 数据
                    parties['illegalPartiesClauseList'] = ''
                    parties['punishTypeCheckBoxList'] = ''
                    parties['illegalPartiesCerts'] = ''

                    #print("插入SQl语句：", insert_parties)
                    #print("插入值：", tuple(parties.values()))

                    cursor.execute(insert_parties, tuple(parties.values()))

                #插入详情调查人表
                for enforceRecord in enforceRecordList:
                    # 获取需要插入的字段名称
                    enforceRecord_fields = ', '.join(enforceRecord.keys())
                    print("插入详情调查人员表:", tuple(enforceRecord.values()))
                    # 拼装需要插入字段对应的%号个数
                    enforceRecord_values = ", ".join(['%s'] * len(enforceRecord))
                    # 判断是否包含 "None"，如果包含则替换为 "null"
                    #if "None" in enforceRecord:
                     #   enforceRecord = enforceRecord.replace("None", "null")
                    insert_enforceRecord = (
                        "INSERT INTO `{table}` ({fields}) "
                        "VALUES ({values})"
                    ).format(
                        table=table_enforceRecord,
                        fields=enforceRecord_fields,
                        values=enforceRecord_values
                    )

                    cursor.execute(insert_enforceRecord, tuple(enforceRecord.values()))


    # 提交事务
    connection.commit()
finally:
    # 关闭数据库连接
    connection.close()



